from mysqlPy import MySQLDatabase
from datetime import datetime
from typing import AnyStr
import requests
import json
import time

def getUserAllGuards(roomid: AnyStr, ruid: AnyStr, page: int = 1):
    cookies = {
        'buvid3': '16E680AC-D635-167E-E5AB-55E09BB2422B80627infoc',
        'b_nut': '1742005280',
        '_uuid': '6AF62310A-2B3B-BB6D-4D47-7D75738DE59D75796infoc',
        'buvid_fp': 'b7767c467374bbe6d59aa1fc04af00d6',
        'buvid4': 'AE49E796-0799-55A6-2042-DD50EBB403BB81834-025031502-3JIWqK%2FcPaIok7AzeYtYhA%3D%3D',
        'header_theme_version': 'CLOSE',
        'enable_web_push': 'DISABLE',
        'enable_feed_channel': 'ENABLE',
        'home_feed_column': '4',
        'browser_resolution': '1272-602',
        'SESSDATA': '6f0d272a%2C1757557304%2Ce8c07%2A32CjCoZeZhGQkTYkv9IENDCX01oIv_q1Q9adFngN0Cm9D23CjoKJS0qWaYwuEA1SJ5dK0SVldZbEN3MGZKQkYzckRjSFphM3hUa0I1al9jaFJiWWI2dXN2c1g5UGRVQnNxRWU3X1ZjS0wwZzJWdk96N2l4U192WnZhTHRIdXBzWWV4UUIzZEtiMDZ3IIEC',
        'bili_jct': '6834aaf98c10ef7bcb62cd82b5d877e8',
        'DedeUserID': '3546869426817357',
        'DedeUserID__ckMd5': '473aaa760a206035',
        'sid': '6mtipbmx',
        'bili_ticket': 'eyJhbGciOiJIUzI1NiIsImtpZCI6InMwMyIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3NDIyNjU3MDYsImlhdCI6MTc0MjAwNjQ0NiwicGx0IjotMX0.QQRwPU6rlOZj7M9-TUGSGqyVaW5fezmP26bCZCxhxfc',
        'bili_ticket_expires': '1742265646',
        'CURRENT_FNVAL': '16',
        'bp_t_offset_3546869426817357': '1044435776080707584',
        'PVID': '2',
        'b_lsid': '83110ACF3_195984172FE',
    }

    headers = {
        'accept': 'application/json, text/plain, */*',
        'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',
        'cache-control': 'no-cache',
        'origin': 'https://live.bilibili.com',
        'pragma': 'no-cache',
        'priority': 'u=1, i',
        'referer': 'https://live.bilibili.com/22749172?hotRank=0&session_id=7dc47192b1236ff67f39c568ce67d628_A84D0980-8ED9-48F6-994F-629743A9B8C5&live_from=77002&visit_id=81qwjrudylo0',
        'sec-ch-ua': '"Chromium";v="134", "Not:A-Brand";v="24", "Microsoft Edge";v="134"',
        'sec-ch-ua-mobile': '?0',
        'sec-ch-ua-platform': '"Windows"',
        'sec-fetch-dest': 'empty',
        'sec-fetch-mode': 'cors',
        'sec-fetch-site': 'same-site',
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36 Edg/134.0.0.0',
        # 'cookie': "Hm_lvt_8a6e55dbd2870f0f5bc9194cddf32a02=1716289769,1716295470,1716540554; buvid3=8D323198-059C-21CE-2167-7F7D859EFC7151610infoc; b_nut=1739513551; _uuid=CF710AB18-EE69-1BA10-723D-5D3CD1015DB8969552infoc; rpdid=|(J~R)ulRlYJ0J'u~JmluJllJ; DedeUserID=491901755; DedeUserID__ckMd5=b119362fab0f4c20; header_theme_version=CLOSE; enable_web_push=DISABLE; buvid4=263695BF-7C46-06E8-0B91-8F0590B7E4D990901-022012422-zfc%2B%2BopW3ru1dt%2FGhhf%2BBQ%3D%3D; LIVE_BUVID=AUTO9417395306626729; hit-dyn-v2=1; buvid_fp_plain=undefined; is-2022-channel=1; enable_feed_channel=ENABLE; fingerprint=b7767c467374bbe6d59aa1fc04af00d6; SESSDATA=0e7d930d%2C1757382410%2Cce977%2A32CjBU-ukMAa-SqpIRWTODoCPMdZjVuZ7tdcbfeBqvh684zj4rXVQeU3GAXI42kDBV_T0SVm1MTlFBWTlnZzFzdHZidm5maFVpbU1razJrVUdzSnY3RU9wY1kzOE9xX3FCcV9hdS00S19hdXlzTXRmaUJPQUo2bnpCNzYyUTNDbTFwWTh2VXNXWlZBIIEC; bili_jct=7ff013e3be0d3b5a70cb2777c7144c23; bili_ticket=eyJhbGciOiJIUzI1NiIsImtpZCI6InMwMyIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3NDIxMjIyMDYsImlhdCI6MTc0MTg2Mjk0NiwicGx0IjotMX0.4HIn9ghRlkHCPUwIO7UxON-d17hd8y5VWzo9ogWCRGA; bili_ticket_expires=1742122146; browser_resolution=1232-602; home_feed_column=4; CURRENT_QUALITY=80; buvid_fp=b7767c467374bbe6d59aa1fc04af00d6; CURRENT_FNVAL=4048; b_lsid=E12EDCE9_1959C869D41; bp_t_offset_491901755=1044749089649983488; PVID=5",
    }
    # 后端有分页大小限制，最大30
    params = {
        'roomid': roomid,
        'page': page,
        'ruid': ruid,
        'page_size': '30',
        'typ': '5', # 总榜 月榜 周榜？
        'platform': 'web',
    }

    response = requests.get(
        'https://api.live.bilibili.com/xlive/app-room/v2/guardTab/topListNew',
        params=params,
        cookies=cookies,
        headers=headers,
    )
    """
    TODO
    data: json.loads(response.text)['data']
    info :data['info']
    list: data['list']
    top3: data['top3']

    num: info.num 大航海人数
    singleUser: list[0] and top3[0]
    ruid: singleUser['ruid']
    rank: singleUser['rank'] 排名 - 粉丝牌等级
    accompany: singleUser['accompany'] 陪伴值
    level: singleUser['guard']['level']
    """
    return response

if __name__ == "__main__":
    # 配置数据库信息
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "111111",
        "database": "gardevoir",
        "port": 3306
    }

    target_usernames = [
        "又一充电中",
        "梨安不迷路",
        "沐霂是MUMU呀",
        "恬豆发芽了"
    ]

    # getUserAllGuards("23771092","1217754423")

    # 使用上下文管理器自动管理连接
    with MySQLDatabase(**db_config) as db:
        # 示例查询
        # 构造SQL查询
        sql = """
            SELECT * 
            FROM user 
            WHERE user_name IN ({})
        """.format(','.join(['%s'] * len(target_usernames)))

        # 执行查询
        results = db.execute_query(sql, tuple(target_usernames))

        # 插入语句 rank是关键词用反引号包括
        insert_sql = """
            INSERT INTO guard_user 
                (`create_time`, `uid`, `guard_belong`, `rank`, `accompany`, `guard_level`, `medal_level`) 
            VALUES 
                (%s, %s, %s, %s, %s, %s, %s)
        """
        insert_data = []
        for user in results:
            first_flag = True
            room_id = user["room_id"]
            m_id = user["m_id"]
            response = getUserAllGuards(room_id, m_id)
            page = 1
            nowPage = json.loads(response.text)['data']['info']['now']
            allPage = json.loads(response.text)['data']['info']['page']
            current_time = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
            while(nowPage <= allPage):
                if response.status_code == 200:
                    responseData = json.loads(response.text)['data']
                    if first_flag:
                        for guard_user in responseData['top3']:
                            insert_data.append(
                                (current_time, guard_user['uinfo']['uid'], m_id, guard_user['rank'],
                                 guard_user['accompany'], guard_user['uinfo']['guard']['level'],
                                 guard_user['uinfo']['medal']['level'])
                            )
                    for guard_user in responseData['list']:
                        insert_data.append(
                            (current_time, guard_user['uinfo']['uid'], m_id, guard_user['rank'],
                             guard_user['accompany'], guard_user['uinfo']['guard']['level'],
                             guard_user['uinfo']['medal']['level'])
                        )
                    first_flag = False
                    time.sleep(0.3)
                    page += 1
                    response = getUserAllGuards(room_id, m_id, page)
                    nowPage = json.loads(response.text)['data']['info']['now']
                    allPage = json.loads(response.text)['data']['info']['page']
                else:
                    print("大航海获取失败")

        inserted = db.execute_many(insert_sql, insert_data)
        db.commit()
        print(f"成功插入 {inserted} 条记录")




